package net.hn.hnms.biz.safety.monitor.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.hn.hnms.biz.safety.monitor.domain.LongruanAlarmListPo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 龙软煤矿报警表
 */
public interface LongruanAlarmListMapper extends BaseMapper<LongruanAlarmListPo> {
    @Select("select * from \"longruan_alarm\" where data_time = (select max(data_time) from \"longruan_alarm\")")
    List<LongruanAlarmListPo> selectMaxDateList();

    @Select("WITH date_series AS (\n" +
            "    SELECT generate_series(\n" +
            "        current_date - interval '9 days',\n" +
            "        current_date,\n" +
            "        '1 day'::interval\n" +
            "    )::date AS data_time\n" +
            ")\n" +
            "SELECT \n" +
            "    d.data_time,\n" +
            "    COALESCE(MAX(l.ch4_warn_count), 0) AS ch4_warn_count,\n" +
            "    COALESCE(MAX(l.co_warn_count), 0) AS co_warn_count\n" +
            "FROM date_series d\n" +
            "LEFT JOIN \"aqjk\".\"longruan_alarm\" l ON DATE(l.data_time) = d.data_time\n" +
            "GROUP BY d.data_time\n" +
            "ORDER BY d.data_time;")
    List<LongruanAlarmListPo> selectListMaxAlarmList();

    /**
     * 获取安全监控报警最近一周内的超限
     * @return
     */
    @Select("WITH date_series AS (\n" +
            "    SELECT generate_series(\n" +
            "        current_date - interval '6 days',\n" +
            "        current_date,\n" +
            "        '1 day'::interval\n" +
            "    )::date AS data_time\n" +
            ")\n" +
            "SELECT \n" +
            "    d.data_time,\n" +
            "    COALESCE(MAX(l.ch4_warn_count), 0) AS ch4_warn_count,\n" +
            "    COALESCE(MAX(l.co_warn_count), 0) AS co_warn_count\n" +
            "FROM date_series d\n" +
            "LEFT JOIN \"aqjk\".\"longruan_alarm\" l ON DATE_TRUNC('day', l.data_time) = d.data_time\n" +
            "GROUP BY d.data_time\n" +
            "ORDER BY d.data_time;")
    List<LongruanAlarmListPo> selectListWeekMaxAlarmList();

    /**
     * 获取安全监控报警最近一月内的超限
     * @return
     */    @Select("WITH date_series AS (\n" +
            "    SELECT generate_series(\n" +
            "        current_date - interval '29 days',\n" +
            "        current_date,\n" +
            "        '1 day'::interval\n" +
            "    )::date AS data_time\n" +
            ")\n" +
            "SELECT \n" +
            "    d.data_time,\n" +
            "    COALESCE(MAX(l.ch4_warn_count), 0) AS ch4_warn_count,\n" +
            "    COALESCE(MAX(l.co_warn_count), 0) AS co_warn_count\n" +
            "FROM date_series d\n" +
            "LEFT JOIN \"aqjk\".\"longruan_alarm\" l ON DATE_TRUNC('day', l.data_time) = d.data_time\n" +
            "GROUP BY d.data_time\n" +
            "ORDER BY d.data_time;")
    List<LongruanAlarmListPo> selectListMonthMaxAlarmList();
}
